﻿using H2.NET.Common;
using H2.NET.Web.API.Common;
using NPOI.HSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace H2.NET.Web.Pages.SMD
{
    public partial class EquipmentStockReport : System.Web.UI.Page
    {
        #region 变量 定义
        [SessionType]
        public clsCommon clsCmm = new clsCommon();
        public int int_width = 0;
        public DataTable dt_field
        {
            get
            {
                return this.ViewState["dt_field"] == null ? new DataTable() : (DataTable)this.ViewState["dt_field"];
            }
            set
            {
                this.ViewState["dt_field"] = value;
            }
        }

        public string order_by_seq
        {
            get
            {
                return this.ViewState["order_by_seq"] == null ? "ASC" : (String)this.ViewState["order_by_seq"];
            }
            set
            {
                this.ViewState["order_by_seq"] = value;
            }
        }

        public string whereSql
        {
            get
            {
                return this.ViewState["whereSql"] == null ? " and 1=1" : this.ViewState["whereSql"].ToString();
            }
            set
            {
                this.ViewState["whereSql"] = value;
            }
        }
        #endregion
        protected void Page_Load(object sender, EventArgs e)
        {
            // Request.Url.ToString() + "&id=1";

            var action = AksRequest.GetQueryString("action");
            if (action == "export")
            {
               // Export("export-" + System.DateTime.Now.ToString("yyyyMMdd") + ".xls");
            }

        }
        #region 方法 执行实际报表数据的取得
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        private void bind_gv_report(string whereSql)
        {
            var sql = "";
            DataTable dt = new DataTable();
            try
            {

                clsCmm.clsDB.Storage("oleDB");
                sql = string.Format(@"select  b.EapproveSN  AS '设备流水号',b.region as '区域',a.AuthID as '授权号', a.Amount as '保证金金额',a.FullSetSN as '固定资产号',CONVERT(varchar, a.InstallDate, 120 ) as '安装日期',b.Province as '省',b.City as '市',b.StoreName as '门店名称',d.AfterValue as '设备状态',CONVERT(varchar, d.ChangeTime, 120 ) as '暂存日期'
                                  from AkzoNobelSMD..v_DeviceDeposit a,t_smd_Relation b,t_Assessment c,t_StoreDataChangeLog d
                                  where a.FullSetSN=b.FullsetSN and b.BusinessKey=c.BusinessKey and b.BusinessProcess=c.BusinessProcess and c.ID=d.AssessmentID  and d.AfterValue='经销商处寄存'" + whereSql); //
                dt = clsCmm.clsDB.Query(sql);

                this.GridView1.DataSource = dt;
                this.GridView1.DataBind();

                #region 导航栏初始化

                //获取Label实例，显示页次信息
                int rowCount = dt.Rows.Count;

                Label lblCurrent = this.lblPageCurrent;
                Label lblCount = this.lblPageCount;
                Label lblRow = this.lblPageRow;
                //获取按钮实例，为了控制其是否可用
                LinkButton btnFirstTem = this.btnFirst;
                LinkButton btnPrevTem = this.btnPrev;
                LinkButton btnNextTem = this.btnNext;
                LinkButton btnLastTem = this.btnLast;

                if (lblCurrent != null)
                    lblCurrent.Text = (this.GridView1.PageIndex + 1).ToString();
                if (lblCount != null)
                {
                    if (this.GridView1.PageCount.ToString() != "0" && this.GridView1.PageCount.ToString() != "")
                        lblCount.Text = this.GridView1.PageCount.ToString();
                    else
                        lblCount.Text = (this.GridView1.PageCount + 1).ToString();
                }
                if (lblRow != null)
                {
                    lblRow.Text = rowCount.ToString();
                }

                //只有一页，所有分页按钮不可用
                if (this.GridView1.PageCount <= 1)
                {
                    btnFirstTem.Visible = false;
                    btnPrevTem.Visible = false;
                    btnNextTem.Visible = false;
                    btnLastTem.Visible = false;
                    btnGoTo.Visible = false;
                    drpGoTo.Visible = false;
                }
                else
                {
                    //加载 GoTo 下拉框
                    btnGoTo.Visible = true;
                    drpGoTo.Visible = true;
                    drpGoTo.Items.Clear();
                    for (int i = 0; i < this.GridView1.PageCount; i++)
                    {
                        drpGoTo.Items.Add((i + 1).ToString());
                    }

                    drpGoTo.SelectedIndex = this.GridView1.PageIndex;

                    btnFirstTem.Visible = true;
                    btnPrevTem.Visible = true;
                    btnNextTem.Visible = true;
                    btnLastTem.Visible = true;
                    if (this.GridView1.PageIndex == 0)
                    {
                        btnFirstTem.Enabled = false;
                        btnPrevTem.Enabled = false;
                        btnNextTem.Enabled = true;
                        btnLastTem.Enabled = true;
                    }
                    else if (this.GridView1.PageIndex == (this.GridView1.PageCount - 1))
                    {
                        btnFirstTem.Enabled = true;
                        btnPrevTem.Enabled = true;
                        btnNextTem.Enabled = false;
                        btnLastTem.Enabled = false;
                    }
                    else
                    {
                        btnFirstTem.Enabled = true;
                        btnPrevTem.Enabled = true;
                        btnNextTem.Enabled = true;
                        btnLastTem.Enabled = true;
                    }
                }
                #endregion

            }
            catch (Exception ex)
            {
                TaskDB.Log("", false, "调用EquipmentToStockReport.aspx页面bind_gv_report方法-模板-SQL：" + sql);
            }
        }
        #endregion

        public void LoadDataTable()
        {

        }

        #region 事件 值变化 页码
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void drpGoTo_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                this.GridView1.PageIndex = drpGoTo.SelectedIndex;

                //查询
                bind_gv_report(whereSql);

            }
            catch (Exception)
            {

            }
        }
        #endregion 

        #region 事件 单击导航页
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void NavigateToPage(object sender, CommandEventArgs e)
        {
            //控制转页
            switch (e.CommandArgument.ToString())
            {
                case "First":
                    this.GridView1.PageIndex = 0;
                    break;
                case "Prev":
                    if (this.GridView1.PageIndex > 0)
                        this.GridView1.PageIndex -= 1;
                    break;
                case "Next":
                    if (this.GridView1.PageIndex < (this.GridView1.PageCount - 1))
                        this.GridView1.PageIndex += 1;
                    break;
                case "Last":
                    this.GridView1.PageIndex = this.GridView1.PageCount - 1;
                    break;
            }

            //重新加载列表
            bind_gv_report(whereSql);
        }
        #endregion

        #region 事件 单击GridView排序
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void gv_rpt_run_report_Sorting(object sender, GridViewSortEventArgs e)
        {

            //保存排序字段
            this.order_by_seq = e.SortExpression;

            //保存排序状态
            if (this.order_by_seq == "ASC")
            {
                this.order_by_seq = "DESC";
            }
            else
            {
                this.order_by_seq = "ASC";
            }

            //重新加载数据
            bind_gv_report(whereSql);
        }
        #endregion

        #region 方法 初始化报表标题、宽度、对齐方式等
        private void init_report_field(System.Web.UI.WebControls.GridView gv, string str_cfg_no, string str_show_export)
        {
            try
            {
                int_width = 0;
                int int_width_tmp = 0;

                //清空报表保存项
                clsCmm.clsRpt.Clear();

                //报表配置(字段名称)
                string strSQL = "select a.cfg_f_no";
                strSQL += "      ,a.cfg_f_name";
                strSQL += "      ,a.cfg_f_name_cn";
                strSQL += "      ,a.cfg_f_name_en";
                strSQL += "      ,a.cfg_f_align";
                strSQL += "      ,a.cfg_f_width";
                strSQL += "      ,a.cfg_f_sequence";
                strSQL += "      ,a.cfg_f_res_no";
                strSQL += "      ,a.cfg_f_is_show";
                strSQL += "      ,a.cfg_f_is_export";
                strSQL += "      ,a.cfg_f_link_report";
                strSQL += "  from t_report_field a";
                strSQL += " where a.status = 1";
                strSQL += "   and a.cfg_f_fk_cfg_no = 'MDC-1111'";
                strSQL += " order by cfg_f_sequence";
                clsCmm.clsDB.Storage("smdDB");
                dt_field = clsCmm.clsDB.Query(strSQL);

                //清空列表                
                gv.Columns.Clear();
                gv.AutoGenerateColumns = false;

                //是否隐藏标题
                if (dt_field.Rows.Count > 0 && dt_field.Rows[0]["cfg_f_name_en"].ToString().ToLower() == "hide")
                {
                    gv.HeaderStyle.CssClass = "gv_head_hide";
                }

                for (int i = 0; i < dt_field.Rows.Count; i++)
                {
                    BoundField bf_column = new BoundField();
                    bf_column.DataField = dt_field.Rows[i]["cfg_f_name"].ToString();
                    bf_column.SortExpression = dt_field.Rows[i]["cfg_f_name"].ToString();
                    bf_column.ItemStyle.Wrap = false;

                    //标题
                    if (clsCmm.comm_language == "zh-cn")
                    {
                        bf_column.HeaderText = dt_field.Rows[i]["cfg_f_name_cn"].ToString();
                    }
                    else
                    {
                        bf_column.HeaderText = dt_field.Rows[i]["cfg_f_name_en"].ToString();
                    }

                    //是否显示
                    if (str_show_export == "show")
                    {
                        if (dt_field.Rows[i]["cfg_f_is_show"].ToString() == "1")
                        {
                            bf_column.Visible = true;
                        }
                        else
                        {
                            bf_column.Visible = false;
                        }
                    }

                    //是否导出
                    if (str_show_export == "export")
                    {
                        if (dt_field.Rows[i]["cfg_f_is_export"].ToString() == "1")
                        {
                            bf_column.Visible = true;
                        }
                        else
                        {
                            bf_column.Visible = false;
                        }
                    }

                    //保存当前列报表权限对应值
                    //  save_rpt_right_value(dt_field.Rows[i]["cfg_f_name"].ToString().Trim(), dt_field.Rows[i]["cfg_f_res_no"].ToString().Trim());

                    //列宽                    
                    bf_column.HeaderStyle.Width = new Unit(dt_field.Rows[i]["cfg_f_width"].ToString());
                    int.TryParse(dt_field.Rows[i]["cfg_f_width"].ToString(), out int_width_tmp);
                    int_width += int_width_tmp;

                    //对齐方式
                    bf_column.HeaderStyle.HorizontalAlign = clsCmm.clsRpt.get_align_by_key(dt_field.Rows[i]["cfg_f_align"].ToString());
                    bf_column.ItemStyle.HorizontalAlign = clsCmm.clsRpt.get_align_by_key(dt_field.Rows[i]["cfg_f_align"].ToString());

                    bf_column.HeaderStyle.Font.Size = FontUnit.Point(9);
                    bf_column.HeaderStyle.Font.Bold = true;
                    bf_column.HeaderStyle.Font.Name = "Arial";
                    bf_column.ItemStyle.Font.Size = FontUnit.Point(9);
                    bf_column.ItemStyle.Font.Name = "Arial";
                    bf_column.HtmlEncode = false;

                    gv.Columns.Add(bf_column);
                }

                //this.cfg_width = (int_width + 50).ToString() + "px";
                //this.gv_rpt_run_report.Width = Unit.Pixel((int_width + 50));
                ////设置总宽度
                //gv.Width = new Unit(int_width);
            }
            catch (Exception ee)
            {
                TaskDB.Log("", false, "调用 rpt_run_report.aspx 页面 init_gv_field 方法出错：" + ee.Message);
            }
        }
        #endregion

        public void Export(string str_file_name, string whereSql_1)
        {
            try
            {
                DataTable exp_dt;
                clsCmm.clsDB.Storage("oleDB");
                var sql = string.Format(@"select  b.EapproveSN  AS DeviceEapproveSN,b.region,a.AuthID, a.Amount,a.FullSetSN,a.InstallDate,b.Province,b.City,b.StoreName,d.AfterValue,d.ChangeTime
                                      from AkzoNobelSMD..v_DeviceDeposit a,t_smd_Relation b,t_Assessment c,t_StoreDataChangeLog d
                                      where a.FullSetSN=b.FullsetSN and b.BusinessKey=c.BusinessKey and b.BusinessProcess=c.BusinessProcess and c.ID=d.AssessmentID  and d.AfterValue='经销商处寄存'" + whereSql_1); //

                exp_dt = clsCmm.clsDB.Query(sql); 
                string strSQL = "select a.cfg_f_no";
                strSQL += "      ,a.cfg_f_name";
                strSQL += "      ,a.cfg_f_name_cn";
                strSQL += "      ,a.cfg_f_name_en";
                strSQL += "      ,a.cfg_f_align";
                strSQL += "      ,a.cfg_f_width";
                strSQL += "      ,a.cfg_f_sequence";
                strSQL += "      ,a.cfg_f_res_no";
                strSQL += "      ,a.cfg_f_is_show";
                strSQL += "      ,a.cfg_f_is_export";
                strSQL += "      ,a.cfg_f_link_report";
                strSQL += "  from t_report_field a";
                strSQL += " where a.status = 1";
                strSQL += "   and a.cfg_f_fk_cfg_no = 'MDC-1111'";
                strSQL += " order by cfg_f_sequence";
                dt_field = clsCmm.clsDB.Query(strSQL);

                Response.Expires = 0;
                Response.Clear();
                Response.Buffer = true;
                Response.Charset = "utf-8";
                Response.ContentEncoding = System.Text.Encoding.UTF8;
                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("Content-Disposition", "attachment; filename=" + str_file_name);

                //引用這三個xmlns  
                Response.Write("<html xmlns:o='urn:schemas-microsoft-com:office:office'");
                Response.Write("xmlns:x='urn:schemas-microsoft-com:office:excel'");
                Response.Write("xmlns='http://www.w3.org/TR/REC-html40'>");
                Response.Write("<meta http-equiv=Content-Type content=text/html;charset=utf-8>");

                //在head中加入xml定義  
                Response.Write("\n <head>");
                Response.Write("\n <xml>");
                Response.Write("\n <x:ExcelWorkbook>");
                Response.Write("\n <x:ExcelWorksheets>");
                Response.Write("\n <x:ExcelWorksheet>");
                Response.Write("\n <x:Name>Export Report</x:Name>");

                //以下針對此工作表進行屬性設定  
                Response.Write("\n <x:WorksheetOptions>");
                Response.Write("\n <x:FrozenNoSplit/>");

                //設定凍結行號  
                if (!(dt_field.Rows.Count > 0 && dt_field.Rows[0]["cfg_f_name_en"].ToString().ToLower() == "hide"))
                {
                    Response.Write("\n <x:SplitHorizontal>1</x:SplitHorizontal>");
                }

                //設定卷軸起始行號  
                Response.Write("\n <x:TopRowBottomPane>2</x:TopRowBottomPane>");
                Response.Write("\n <x:ActivePane>2</x:ActivePane>");
                Response.Write("\n </x:WorksheetOptions>");
                Response.Write("\n </xml>");
                Response.Write("\n </head>");
                Response.Write("\n <body>");
                Response.Write("<table borderColor=black border=1>");

                //是否隐藏标题
                if (!(dt_field.Rows.Count > 0 && dt_field.Rows[0]["cfg_f_name_en"].ToString().ToLower() == "hide"))
                {
                    Response.Write("\n <tr>");
                }


                //去除非导出列
                for (int i = 0; i < exp_dt.Columns.Count; i++)
                {
                    bool Flg = true;

                    for (int j = 0; j < dt_field.Rows.Count; j++)
                    {
                        if (exp_dt.Columns[i].ColumnName.ToString().ToLower() == dt_field.Rows[j]["cfg_f_name"].ToString().ToLower() && dt_field.Rows[j]["cfg_f_is_export"].ToString() == "1")
                        {
                            Flg = false;
                            break;
                        }
                    }

                    if (Flg)
                    {
                        exp_dt.Columns.Remove(exp_dt.Columns[i]);
                        exp_dt.AcceptChanges();
                        i--;

                    }
                }

                int tRowCount = exp_dt.Rows.Count;
                int tColumnCount = exp_dt.Columns.Count;

                //是否隐藏标题
                if (!(dt_field.Rows.Count > 0 && dt_field.Rows[0]["cfg_f_name_en"].ToString().ToLower() == "hide"))
                {
                    for (int i = 0; i < tColumnCount; i++)
                    {
                        bool flg = true;
                        for (int j = 0; j < dt_field.Rows.Count; j++)
                        {
                            if (exp_dt.Columns[i].ColumnName.ToString().ToLower() == dt_field.Rows[j]["cfg_f_name"].ToString().ToLower())
                            {
                                flg = false;
                                Response.Write("\n <td align=\"" + (dt_field.Rows[j]["cfg_f_align"].ToString() == "NotSet" ? "left" : dt_field.Rows[j]["cfg_f_align"].ToString().ToLower()) + "\" bgcolor = \"#6DE875\">");
                                Response.Write(solve_html(dt_field.Rows[j][clsCmm.comm_language == "zh-cn" ? "cfg_f_name_cn" : "cfg_f_name_en"].ToString()));
                                Response.Write("\n </td>");
                                break;
                            }
                        }

                        if (flg)
                        {
                            Response.Write("\n <td  bgcolor=\"#F6D087\">");
                            Response.Write(solve_html(exp_dt.Columns[i].ColumnName.ToString()));
                            Response.Write("\n </td>");
                        }
                    }
                }
                //modified by zk 2010-04-23 end

                //是否隐藏标题
                if (!(dt_field.Rows.Count > 0 && dt_field.Rows[0]["cfg_f_name_en"].ToString().ToLower() == "hide"))
                {
                    Response.Write("\n </tr>");
                }

                for (int j = 0; j < tRowCount; j++)
                {
                    Response.Write("\n <tr>");
                    for (int k = 0; k < tColumnCount; k++)
                    {
                        Response.Write("\n <td align=\"" + (dt_field.Rows[k]["cfg_f_align"].ToString() == "NotSet" ? "left" : dt_field.Rows[k]["cfg_f_align"].ToString().ToLower()) + "\" x:num>");


                        var rowData = exp_dt.Rows[j][k].ToString();
                        if (rowData.StartsWith("0"))
                        {
                            rowData = "'" + rowData;
                        }

                        Response.Write(solve_html(rowData));
                        Response.Write("\n </td>");
                    }
                    Response.Write("\n </tr>");
                }

                Response.Write("</table>");
                Response.Write("</body>");
                Response.Write("</html>");
                HttpContext.Current.ApplicationInstance.CompleteRequest();
            }
            catch (Exception ee)
            {
                TaskDB.Log("", false, "调用 rpt_run_report.aspx 页面 export_to_excel 方法出错：" + ee.Message);
            }
        }

        private string solve_html(string strContent)
        {
            strContent = strContent == null ? "" : strContent;
            return strContent;
        }

        protected void btnShow_Click(object sender, EventArgs e)
        {
            this.ViewState["whereSql"] = null;
            var Region = this.txtRegion.Text;
            var Province = this.txtProvince.Text;
            var City = this.txtCity.Text;
            var Authorization = this.txtAuthorization.Text;
            var Store = this.txtStore.Text;
            var FixedAsset = this.txtFixedAsset.Text;
            var DeviceEapproveSN = this.txtDeviceEapproveSN.Text; //设备流水号
            if (!string.IsNullOrWhiteSpace(Region))
            {
                whereSql += " and b.region like '%" + Region + "%'";
            }
            if (!string.IsNullOrWhiteSpace(Province))
            {
                whereSql += " and b.Province like '%" + Province + "%'";
            }
            if (!string.IsNullOrWhiteSpace(City))
            {
                whereSql += " and b.City like '%" + City + "%'";
            }
            if (!string.IsNullOrWhiteSpace(Authorization))
            {
                whereSql += " and a.AuthID like '" + Authorization + "'";
            }
            if (!string.IsNullOrWhiteSpace(Store))
            {
                whereSql += " and StoreName like '%" + Store + "%'";
            }
            if (!string.IsNullOrWhiteSpace(FixedAsset))
            {
                whereSql += " and a.FullsetSN='" + FixedAsset + "'";
            }

            if (!string.IsNullOrWhiteSpace(DeviceEapproveSN))
            {
                whereSql += " and b.EapproveSN like '" + DeviceEapproveSN + "%'";
            }
            bind_gv_report(whereSql);
        }

        protected void btnReportClick_Click(object sender, EventArgs e)
        {
            string whereSql_1 = " and 1=1";
            var Region = this.txtRegion.Text;
            var Province = this.txtProvince.Text;
            var City = this.txtCity.Text;
            var Authorization = this.txtAuthorization.Text;
            var Store = this.txtStore.Text;
            var FixedAsset = this.txtFixedAsset.Text;
            var DeviceEapproveSN = this.txtDeviceEapproveSN.Text; //设备流水号
            if (!string.IsNullOrWhiteSpace(Region))
            {
                whereSql_1 += " and b.region like '%" + Region + "%'";
            }
            if (!string.IsNullOrWhiteSpace(Province))
            {
                whereSql_1 += " and b.Province like '%" + Province + "%'";
            }
            if (!string.IsNullOrWhiteSpace(City))
            {
                whereSql_1 += " and b.City like '%" + City + "%'";
            }
            if (!string.IsNullOrWhiteSpace(Authorization))
            { 
                whereSql_1 += " and a.AuthID like '" + Authorization + "'";
            }
            if (!string.IsNullOrWhiteSpace(Store))
            {
                whereSql_1 += " and StoreName like '%" + Store + "%'";
            }
            if (!string.IsNullOrWhiteSpace(FixedAsset))
            {
                whereSql_1 += " and a.FullsetSN='" + FixedAsset + "'";
            }
            if (!string.IsNullOrWhiteSpace(DeviceEapproveSN))
            {
                whereSql_1 += " and b.EapproveSN like '" + DeviceEapproveSN + "%'";
            }

            Export("export-" + System.DateTime.Now.ToString("yyyyMMdd") + ".xls", whereSql_1);
        }
    }
}